mod calc_conf;
mod sql_parse;
mod sqls_conf;
use aho_corasick::AhoCorasick;
use calc_conf::{get_calc_sqls, get_calc_sqls_by_divi_type, get_ref_calc_sqls, init_big_table_calc};
use chrono::NaiveDate;
use sqls_conf::{init_big_table_sqls, sum_to_empl, SqlItem};
use crate::base::{get_ch_pool, DiviType, SourceType};
pub (crate) use self::sqls_conf::{is_big_table_init,get_big_table_sqls};

pub(crate) struct BigTable {
    sql_items: Vec<&'static SqlItem>,
}
impl BigTable {
    pub fn new(source: SourceType, name: &str) -> Self {
        let sql_items = get_big_table_sqls(source, name);
        Self { sql_items }
    }
    pub(crate) async fn clear(&self, date: &NaiveDate) -> anyhow::Result<&BigTable> {
        if self.sql_items.len() == 0 {
            return Ok(self);
        }
        let mut client = get_ch_pool().get_handle().await?;
        self.clear_with_back(&mut client, date, false).await?;
        Ok(self)
    }
    pub(crate) async fn build(&self, date: &NaiveDate) -> anyhow::Result<()> {
        if self.sql_items.len() == 0 {
            return Ok(());
        }
        let mut client = get_ch_pool().get_handle().await?;
        self.build_with_back(&mut client, date, false).await
    }
    async fn clear_with_back(
        &self,
        client: &mut clickhouse_rs::ClientHandle,
        date: &NaiveDate,
        is_back: bool,
    ) -> anyhow::Result<()> {
        for sql_item in self.sql_items.iter() {
            let can = sql_item.can_run(date).await?;
            if !can {
                continue;
            }
            let back_where = get_back_where(&sql_item.divi_type, is_back);
            sql_item.delete(client, date, &back_where).await?;
        }
        Ok(())
    }
    async fn build_with_back(
        &self,
        client: &mut clickhouse_rs::ClientHandle,
        date: &NaiveDate,
        is_back: bool,
    ) -> anyhow::Result<()> {
        let search_vals = &["{date}", "{back_where}"];
        let ac = AhoCorasick::builder().build(search_vals).unwrap();
        for sql_item in self.sql_items.iter() {
            let can = sql_item.can_run(date).await?;
            if !can {
                continue;
            }
            let back_where = get_back_where(&sql_item.divi_type, is_back);
            sql_item.insert(client, date, &back_where, &ac).await?;
            sum_to_empl(
                client,
                &sql_item.divi_type,
                &sql_item.col_names,
                date,
                &back_where,
            )
            .await?;
        }
        Ok(())
    }
    pub(crate) async fn back_clear(
        &self,
        sdate: &NaiveDate,
        edate: &NaiveDate,
    ) -> anyhow::Result<()> {
        if self.sql_items.len() == 0 {
            return Ok(());
        }
        let mut curr_date = sdate.clone();
        let mut client = get_ch_pool().get_handle().await?;
        while curr_date <= *edate {
            self.clear_with_back(&mut client, &curr_date, true).await?;
            curr_date = curr_date + chrono::Duration::days(1);
        }
        Ok(())
    }
    pub(crate) async fn back_build(
        &self,
        sdate: &NaiveDate,
        edate: &NaiveDate,
    ) -> anyhow::Result<()> {
        if self.sql_items.len() == 0 {
            return Ok(());
        }
        let mut curr_date = sdate.clone();
        let mut client = get_ch_pool().get_handle().await?;
        while curr_date <= *edate {
            self.build_with_back(&mut client, &curr_date, true).await?;
            curr_date = curr_date + chrono::Duration::days(1);
        }
        Ok(())
    }
}

fn get_back_where(dt: &DiviType, is_back: bool) -> String {
    let back_where: String = if is_back {
        let tname = dt.back_table_name();
        if *dt == DiviType::CompBizFee {
            format!(" and CUID in (select CUID from T_CM_BIZ_FEE_SUM_M where JLID in (select JLID from {tname}) and KHLX='0')")
        } else if *dt == DiviType::IndiBizFee {
            format!(" and CUID in (select CUID from T_CM_BIZ_FEE_SUM_M where JLID in (select JLID from {tname}) and KHLX='1')")
        } else {
            format!(" and CUID in (select CUID from {tname})")
        }
    } else {
        "".to_owned()
    };
    back_where
}
pub (crate) async fn calc_proc(date: &NaiveDate,is_back: bool) -> anyhow::Result<()> {
    let search_vals = &["{date}", "{back_where}"];
    let ac = AhoCorasick::builder().build(search_vals).unwrap();
    let mut client = get_ch_pool().get_handle().await?;
    for c in get_calc_sqls() {
        let back_where = get_back_where(&c.divi_type, is_back);
        c.run(&mut client,date,&back_where,&ac).await?;
    }
    Ok(())
}
pub (crate) async fn calc_proc_with_divi_type(sdate: &NaiveDate,edate: &NaiveDate,divi_type: &DiviType,is_back: bool) -> anyhow::Result<()> {
    let search_vals = &["{date}", "{back_where}"];
    let ac = AhoCorasick::builder().build(search_vals).unwrap();
    let mut client = get_ch_pool().get_handle().await?;
    for c in get_calc_sqls_by_divi_type(divi_type) {
        let back_where = get_back_where(&c.divi_type, is_back);
        let mut curr_date= sdate.clone();
        while curr_date <= *edate {
            c.run(&mut client,&curr_date,&back_where,&ac).await?;
            curr_date = curr_date + chrono::Duration::days(1);
        }
    }
    Ok(())
}
pub (crate) async fn calc_proc_with_ref(date: &NaiveDate,col_names: &Vec<String>,is_back: bool) -> anyhow::Result<()> {
    let search_vals = &["{date}", "{back_where}"];
    let ac = AhoCorasick::builder().build(search_vals).unwrap();
    let mut client = get_ch_pool().get_handle().await?;
    for c in get_ref_calc_sqls(col_names) {
        let back_where = get_back_where(&c.divi_type, is_back);
        c.run(&mut client,date,&back_where,&ac).await?;
    }
    Ok(())
}
pub(crate) async fn asse_ftp_back(sdate: &NaiveDate, edate: &NaiveDate) -> anyhow::Result<()> {
    let mut curr_date = sdate.clone();
    while *sdate <= *edate {
        BigTable::new(SourceType::EDW, "ASSE_FTP_DGCK")
            .clear(&curr_date)
            .await?
            .build(&curr_date)
            .await?;
        BigTable::new(SourceType::EDW, "ASSE_FTP_DGDK")
            .clear(&curr_date)
            .await?
            .build(&curr_date)
            .await?;
        BigTable::new(SourceType::EDW, "ASSE_FTP_CXCK")
            .clear(&curr_date)
            .await?
            .build(&curr_date)
            .await?;
        BigTable::new(SourceType::EDW, "ASSE_FTP_GRDK")
            .clear(&curr_date)
            .await?
            .build(&curr_date)
            .await?;
        curr_date = curr_date + chrono::Duration::days(1);
    }
    Ok(())
}

pub fn init_big_table() -> anyhow::Result<()> {
    init_big_table_sqls()?;
    init_big_table_calc()?;
    Ok(())
}
